package dao

import (
	"fmt"
	"go-mysql/global"
)

type User struct {
	ID   int
	Name string
	Age  int
}

func UpdateById(user User) {
	sqlStr := "update user set age=?,name=? where id = ?"
	ret, err := global.DB.Exec(sqlStr, user.Age, user.Name, user.ID)
	if err != nil {
		fmt.Printf("更新失败,err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected()
	if err != nil {
		fmt.Printf("获取影响行数失败,err:%v\n", err)
		return
	}
	fmt.Printf("更新成功，影响行数为:%d\n", n)
}

func DeleteById(user User) {
	sqlStr := "DELETE FROM user WHERE id = ?"
	ret, err := global.DB.Exec(sqlStr, user.ID)
	if err != nil {
		fmt.Printf("删除数据失败,err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		fmt.Printf("获取影响行数失败,err:%v\n", err)
		return
	}
	fmt.Printf("删除数据成功，影响行数为:%d\n", n)
}

func QueryById(ID int) {
	sqlStr := "SELECT id,name,age from user WHERE id = ?"
	row := global.DB.QueryRow(sqlStr, ID)
	var u User
	//然后使用Scan()方法给对应类型变量赋值，以便取出结果,注意传入的是指针
	err := row.Scan(&u.ID, &u.Name, &u.Age)
	if err != nil {
		fmt.Printf("获取数据错误, err:%v\n", err)
		return
	}
	fmt.Printf("查询数据成功%#v\n", u)
}

func QueryRows() {
	sqlStr := "select id,name,age from user where id>?"
	rows, err := global.DB.Query(sqlStr, 0)
	if err != nil {
		fmt.Println("查询失败,err", err)
		return
	}
	defer rows.Close()
	//循环读取数据
	for rows.Next() {
		var u User
		err := rows.Scan(&u.ID, &u.Name, &u.Age)
		if err != nil {
			fmt.Println("scan失败,err", err)
			return
		}
		fmt.Printf("id:%d	name:%s		age:%d\n", u.ID, u.Name, u.Age)
	}
}

func PrepareQueryRow() {
	sqlStr := "select id,name,age from user where id > ?"
	stmt, err := global.DB.Prepare(sqlStr)
	if err != nil {
		fmt.Println("预处理失败,err", err)
		return
	}
	defer stmt.Close()
	rows, err := stmt.Query(0)
	if err != nil {
		fmt.Println("查询失败,err", err)
		return
	}
	defer rows.Close()
	//循环读取
	for rows.Next() {
		var u User
		err := rows.Scan(&u.ID, &u.Name, &u.Age)
		if err != nil {
			fmt.Println("scan失败,err", err)
			return
		}
		fmt.Printf("id:%d	 name:%s	 age:%d\n", u.ID, u.Name, u.Age)
	}
}

func BatchInsert() {
	sqlStr := "insert into user (name,age) values(?,?)"
	// 把要执行的命令发送给MySQL服务端做预处理
	stmt, err := global.DB.Prepare(sqlStr)
	if err != nil {
		fmt.Printf("预处理失败, err:%v\n", err)
		return
	}
	defer stmt.Close()
	for i := 10; i < 15; i++ {
		name := fmt.Sprintf("name%02d", i)
		stmt.Exec(name, i)
	}
	fmt.Println("批量插入成功")
}

func TransDemo() {
	tx, err := global.DB.Begin()
	if err != nil {
		if tx != nil {
			tx.Rollback() // 回滚
		}
		fmt.Println("事务开启失败,err", err)
		return
	}
	sql1 := "update user set age=age+? where id=?"
	_, err = tx.Exec(sql1, 2, 1)
	if err != nil {
		tx.Rollback()
		fmt.Println("sql1执行失败,err", err)
		return
	}
	sql2 := "update user set age=age-? where id=?"
	_, err = tx.Exec(sql2, 2, 2)
	if err != nil {
		tx.Rollback()
		fmt.Println("sql2执行失败,err", err)
		return
	}
	err = tx.Commit()
	if err != nil {
		tx.Rollback()
		fmt.Println("事务提交失败,err", err)
		return
	}
	fmt.Println("数据更新成功！")
}
